# -*- coding:UTF-8 -*-

import re
import os
import json
import urllib
import urllib2
import pymysql


db = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='lsq910561556Wy',
    db='weixin',
    charset='utf8'
)
cursor = db.cursor()
sql_create = 'create table t3(id int auto_increment primary key,read_num int,like_num int);'
cursor.execute(sql_create)
sql_select = 'select url from t1;'
cursor.execute(sql_select)
urls = cursor.fetchall()
patt = re.compile('http://mp.weixin.qq.com/s\?__biz=(.*?)&mid=(.*?)&idx=(.*?)&sn=(.*?)&chksm=.*#rd')
for url in urls:
    param = re.findall(patt, url[0])
    kwgs = {
        '__biz': param[0][0],
        'mid': param[0][1],
        'idx': param[0][2],
        'sn': param[0][3],
        'scene': '0'
    }
    link = 'http://mp.weixin.qq.com/s?' + urllib.urlencode(kwgs) + '#wechat_redirect'
    url0 = urllib.quote_plus(link)
    appid = 'f3226b220d7a5bb939b3c725b978b434'
    # 调用外部API获取阅读量与点赞量
    url0 = 'https://api.shenjian.io/?appid=' + appid + '&url=' + url0
    request = urllib2.Request(url0)
    response = urllib2.urlopen(request)
    data = json.loads(response.read()).get('data')
    read_num = data.get('article_view_count')
    like_num = data.get('article_agree_count')
    print '%s%s' % ('已获取文章url：'.decode('utf-8').encode('gbk'), link)
    print '%s%s' % ('正在录入文章阅读数与点赞数......'.decode('utf-8').encode('gbk'), os.linesep)
    cursor.execute('insert into t3(read_num,like_num)values(%s,%s)', (read_num, like_num))
    db.commit()
print '数据录入完毕！'.decode('utf-8').encode('gbk')
cursor.close()
db.close()
